/**
 * $RCSfile$
 * $Revision: 3117 $
 * $Date: 2005-11-25 22:57:29 -0300 (Fri, 25 Nov 2005) $
 *
 * Copyright (C) 2004-2008 Jive Software. All rights reserved.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package org.jivesoftware.openfire.group;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.List;

import org.jivesoftware.database.DbConnectionManager;
import org.jivesoftware.openfire.XMPPServer;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.xmpp.packet.JID;

import sun.reflect.generics.reflectiveObjects.NotImplementedException;

/**
 * Database implementation of the GroupManager interface.
 * 
 * @author Matt Tucker
 */
public class DefaultGroupProvider extends AbstractGroupProvider {

	private static final Logger Log = LoggerFactory
			.getLogger(DefaultGroupProvider.class);

	private static final String INSERT_GROUP = "INSERT INTO ofGroup (groupName, description) VALUES (?, ?)";
	private static final String SAVE_GROUP = "UPDATE ofGroup SET description=? WHERE groupName=?";
	private static final String SET_GROUP_NAME_1 = "UPDATE ofGroup SET groupName=? WHERE groupName=?";
	private static final String SET_GROUP_NAME_2 = "UPDATE ofGroupProp SET groupName=? WHERE groupName=?";
	private static final String SET_GROUP_NAME_3 = "UPDATE ofGroupUser SET groupName=? WHERE groupName=?";
	private static final String DELETE_GROUP_USERS = "DELETE FROM ofGroupUser WHERE groupName=?";
	private static final String DELETE_PROPERTIES = "DELETE FROM ofGroupProp WHERE groupName=?";
	private static final String DELETE_GROUP = "DELETE FROM ofGroup WHERE groupName=?";
	private static final String GROUP_COUNT = "SELECT count(*) FROM ofGroup";
	private static final String LOAD_ADMINS = "SELECT username FROM ofGroupUser WHERE administrator=1 AND groupName=? ORDER BY sortNumber";
	private static final String LOAD_MEMBERS = "SELECT username FROM ofGroupUser WHERE administrator=0 AND groupName=? ORDER BY sortNumber";
	private static final String UPDATE_GROUP_USER_SORTNUMBER = "UPDATE ofGroupUser set sortNumber = ? where username = ? and groupName = ?";
	private static final String UPDATE_GROUP_SORTNUMBER = "UPDATE ofGroup set sortNumber = ? where groupName = ?";
	private static final String LOAD_GROUP = "SELECT description FROM ofGroup WHERE groupName=?";
	private static final String REMOVE_USER = "DELETE FROM ofGroupUser WHERE groupName=? AND username=?";
	private static final String ADD_USER = "INSERT INTO ofGroupUser (groupName, username, administrator, sortNumber) VALUES (?, ?, ?, ?)";
	private static final String UPDATE_USER = "UPDATE ofGroupUser SET administrator=? WHERE groupName=? AND username=?";
	private static final String USER_GROUPS = "SELECT groupName FROM ofGroupUser WHERE username=? ";
	private static final String ALL_GROUPS = "SELECT groupName FROM ofGroup ORDER BY sortNumber"; // 以sortNumber排序
	private static final String SEARCH_GROUP_NAME = "SELECT groupName FROM ofGroup WHERE groupName LIKE ? ORDER BY sortNumber";

	private XMPPServer server = XMPPServer.getInstance();

	@Override
	public Group createGroup(String name) {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement(INSERT_GROUP);
			pstmt.setString(1, name);
			pstmt.setString(2, "");
			pstmt.executeUpdate();
		} catch (SQLException e) {
			Log.error(e.getMessage(), e);
		} finally {
			DbConnectionManager.closeConnection(pstmt, con);
		}
		Collection<JID> members = getMembers(name, false);
		Collection<JID> administrators = getMembers(name, true);
		return new Group(name, "", members, administrators);
	}

	@Override
	public Group getGroup(String name) throws GroupNotFoundException {
		String description = null;

		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement(LOAD_GROUP);
			pstmt.setString(1, name);
			rs = pstmt.executeQuery();
			if (!rs.next()) {
				throw new GroupNotFoundException("Group with name " + name
						+ " not found.");
			}
			description = rs.getString(1);
		} catch (SQLException e) {
			Log.error(e.getMessage(), e);
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		Collection<JID> members = getMembers(name, false);
		Collection<JID> administrators = getMembers(name, true);
		return new Group(name, description, members, administrators);
	}

	@Override
	public void setDescription(String name, String description)
			throws GroupNotFoundException {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement(SAVE_GROUP);
			pstmt.setString(1, description);
			pstmt.setString(2, name);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			Log.error(e.getMessage(), e);
			throw new GroupNotFoundException();
		} finally {
			DbConnectionManager.closeConnection(pstmt, con);
		}
	}

	@Override
	public void setName(String oldName, String newName)
			throws GroupAlreadyExistsException {
		Connection con = null;
		PreparedStatement pstmt = null;
		boolean abortTransaction = false;
		try {
			con = DbConnectionManager.getTransactionConnection();
			pstmt = con.prepareStatement(SET_GROUP_NAME_1);
			pstmt.setString(1, newName);
			pstmt.setString(2, oldName);
			pstmt.executeUpdate();
			DbConnectionManager.fastcloseStmt(pstmt);

			pstmt = con.prepareStatement(SET_GROUP_NAME_2);
			pstmt.setString(1, newName);
			pstmt.setString(2, oldName);
			pstmt.executeUpdate();
			DbConnectionManager.fastcloseStmt(pstmt);

			pstmt = con.prepareStatement(SET_GROUP_NAME_3);
			pstmt.setString(1, newName);
			pstmt.setString(2, oldName);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			Log.error(e.getMessage(), e);
			abortTransaction = true;
		} finally {
			DbConnectionManager.closeStatement(pstmt);
			DbConnectionManager.closeTransactionConnection(con,
					abortTransaction);
		}
	}

	@Override
	public void deleteGroup(String groupName) {
		Connection con = null;
		PreparedStatement pstmt = null;
		boolean abortTransaction = false;
		try {
			con = DbConnectionManager.getTransactionConnection();
			// Remove all users in the group.
			pstmt = con.prepareStatement(DELETE_GROUP_USERS);
			pstmt.setString(1, groupName);
			pstmt.executeUpdate();
			DbConnectionManager.fastcloseStmt(pstmt);

			// Remove all properties of the group.
			pstmt = con.prepareStatement(DELETE_PROPERTIES);
			pstmt.setString(1, groupName);
			pstmt.executeUpdate();
			DbConnectionManager.fastcloseStmt(pstmt);

			// Remove the group entry.
			pstmt = con.prepareStatement(DELETE_GROUP);
			pstmt.setString(1, groupName);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			Log.error(e.getMessage(), e);
			abortTransaction = true;
		} finally {
			DbConnectionManager.closeStatement(pstmt);
			DbConnectionManager.closeTransactionConnection(con,
					abortTransaction);
		}
	}

	@Override
	public int getGroupCount() {
		int count = 0;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement(GROUP_COUNT);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			Log.error(e.getMessage(), e);
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		return count;
	}

	@Override
	public Collection<String> getGroupNames() {
		List<String> groupNames = new ArrayList<String>();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement(ALL_GROUPS);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				groupNames.add(rs.getString(1));
			}
		} catch (SQLException e) {
			Log.error(e.getMessage(), e);
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		return groupNames;
	}

	@Override
	public Collection<String> getGroupNames(int startIndex, int numResults) {
		List<String> groupNames = new ArrayList<String>();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = DbConnectionManager.createScrollablePreparedStatement(con,
					ALL_GROUPS);
			rs = pstmt.executeQuery();
			DbConnectionManager.scrollResultSet(rs, startIndex);
			int count = 0;
			while (rs.next() && count < numResults) {
				groupNames.add(rs.getString(1));
				count++;
			}
		} catch (SQLException e) {
			Log.error(e.getMessage(), e);
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		return groupNames;
	}

	@Override
	public Collection<String> getGroupNames(JID user) {
		List<String> groupNames = new ArrayList<String>();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement(USER_GROUPS);
			pstmt.setString(1,
					server.isLocal(user) ? user.getNode() : user.toString());
			rs = pstmt.executeQuery();
			while (rs.next()) {
				groupNames.add(rs.getString(1));
			}
		} catch (SQLException e) {
			Log.error(e.getMessage(), e);
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		return groupNames;
	}

	@Override
	public void addMember(String groupName, JID user, int sortNumber,
			boolean administrator) {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement(ADD_USER);
			pstmt.setString(1, groupName);
			pstmt.setString(2,
					server.isLocal(user) ? user.getNode() : user.toString());
			pstmt.setInt(3, administrator ? 1 : 0);
			pstmt.setInt(4, sortNumber);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			Log.error(e.getMessage(), e);
		} finally {
			DbConnectionManager.closeConnection(pstmt, con);
		}
	}

	@Override
	public void updateMember(String groupName, JID user, boolean administrator) {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement(UPDATE_USER);
			pstmt.setInt(1, administrator ? 1 : 0);
			pstmt.setString(2, groupName);
			pstmt.setString(3,
					server.isLocal(user) ? user.getNode() : user.toString());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			Log.error(e.getMessage(), e);
		} finally {
			DbConnectionManager.closeConnection(pstmt, con);
		}
	}

	@Override
	public void deleteMember(String groupName, JID user) {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement(REMOVE_USER);
			pstmt.setString(1, groupName);
			pstmt.setString(2,
					server.isLocal(user) ? user.getNode() : user.toString());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			Log.error(e.getMessage(), e);
		} finally {
			DbConnectionManager.closeConnection(pstmt, con);
		}
	}

	@Override
	public boolean isReadOnly() {
		return false;
	}

	@Override
	public Collection<String> search(String query) {
		return search(query, 0, Integer.MAX_VALUE);
	}

	@Override
	public Collection<String> search(String query, int startIndex,
			int numResults) {
		if (query == null || "".equals(query)) {
			return Collections.emptyList();
		}
		// SQL LIKE queries don't map directly into a keyword/wildcard search
		// like we want.
		// Therefore, we do a best approximiation by replacing '*' with '%' and
		// then
		// surrounding the whole query with two '%'. This will return more data
		// than desired,
		// but is better than returning less data than desired.
		query = "%" + query.replace('*', '%') + "%";
		if (query.endsWith("%%")) {
			query = query.substring(0, query.length() - 1);
		}

		List<String> groupNames = new ArrayList<String>();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = DbConnectionManager.getConnection();
			if ((startIndex == 0) && (numResults == Integer.MAX_VALUE)) {
				pstmt = con.prepareStatement(SEARCH_GROUP_NAME);
				pstmt.setString(1, query);
				rs = pstmt.executeQuery();
				while (rs.next()) {
					groupNames.add(rs.getString(1));
				}
			} else {
				pstmt = DbConnectionManager.createScrollablePreparedStatement(
						con, SEARCH_GROUP_NAME);
				DbConnectionManager.limitRowsAndFetchSize(pstmt, startIndex,
						numResults);
				pstmt.setString(1, query);
				rs = pstmt.executeQuery();
				// Scroll to the start index.
				DbConnectionManager.scrollResultSet(rs, startIndex);
				int count = 0;
				while (rs.next() && count < numResults) {
					groupNames.add(rs.getString(1));
					count++;
				}
			}
		} catch (SQLException e) {
			Log.error(e.getMessage(), e);
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
		return groupNames;
	}

	@Override
	public boolean isSearchSupported() {
		return true;
	}

	@Override
	public boolean isSharingSupported() {
		return true;
	}

	private Collection<JID> getMembers(String groupName, boolean adminsOnly) {
		List<JID> members = new ArrayList<JID>();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = DbConnectionManager.getConnection();
			if (adminsOnly) {
				pstmt = con.prepareStatement(LOAD_ADMINS);
			} else {
				pstmt = con.prepareStatement(LOAD_MEMBERS);
			}
			pstmt.setString(1, groupName);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				String user = rs.getString(1);
				JID userJID = null;
				if (user.indexOf('@') == -1) {
					// Create JID of local user if JID does not match a
					// component's JID
					if (!server.matchesComponent(userJID)) {
						userJID = server.createJID(user, null);
					}
				} else {
					userJID = new JID(user);
				}
				members.add(userJID);
			}
		} catch (SQLException e) {
			Log.error(e.getMessage(), e);
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
//		System.out.println("adminsOnly =========== " + adminsOnly);
//		System.out.println("groupName =========== " + groupName);
//		System.out.println(this.getClass() + ".getMembers ============ "
//				+ members);
		return members;
	}

	@Override
	public void updateMemberSortNumber(String groupName, JID user, int sortNumber) {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = DbConnectionManager.getConnection();
			PreparedStatement ps = con.prepareStatement(UPDATE_GROUP_USER_SORTNUMBER);
			ps.setInt(1, sortNumber);
			ps.setString(2, server.isLocal(user) ? user.getNode() : user.toString());
			ps.setString(3, groupName);
			ps.executeUpdate();
		} catch (SQLException e) {
			Log.error(e.getMessage(), e);
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
	}
	@Override
    public void updateGroupSortNumber(String groupName, int sortNumber) {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = DbConnectionManager.getConnection();
			PreparedStatement ps = con.prepareStatement(UPDATE_GROUP_SORTNUMBER);
			ps.setInt(1, sortNumber);
			ps.setString(2, groupName);
			ps.executeUpdate();
		} catch (SQLException e) {
			Log.error(e.getMessage(), e);
		} finally {
			DbConnectionManager.closeConnection(rs, pstmt, con);
		}
    }
}